package com.boss.salary.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.framework.exception.AppRuntimeException;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.ISalaryBankPayBackDao;
import com.boss.salary.entity.SalaryBankPayBack;
import com.boss.salary.vo.Offer;
import com.boss.sys.utils.SysUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


@Repository
public class SalaryBankPayBackDaoImpl extends BaseDaoImpl implements ISalaryBankPayBackDao {
    @Override
    public List<SalaryBankPayBack> querySalaryBankPayBackPage(Map<String, Object> queryParam,List<String> childrenId) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" sbpb.id, sbpb.biz_key, sbpb.create_time, sbpb.update_time, sbpb.iden_no, pi.person_name, ")
                .append(" sbpb.old_sala_card_no, sbpb.new_sala_card_no, sbpb.bank_back_code, sbpb.bank_back_remark, ")
                .append(" sbpb.operate_code, sbpb.is_update, sbpb.verified_in_agency_code, sbpb.verified_code, ")
                .append(" sbpb.is_recreate, sbpb.is_long_use, sbpb.is_send, pi.sex_code, pe.sala_card_bank, ai.agency_name, ")
                .append(" ai.id agency_id, ai.agency_code ");
        sql.append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" JOIN ").append(TableName.PERSON_INFO).append(" pi ON sbpb.person_id = pi.id ")
                .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe on pi.id = pe.person_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai on pe.agency_id = ai.id ");
        sql.append(" where sbpb.bank_back_code = ? ");
        paramsList.add(SystemDictConstant.FAILD);
        sql.append(" and ").append(SysUtils.inSql("ai.id", childrenId));
        if (!StringUtils.isEmpty(queryParam)) {
            if(!StringUtils.isEmpty(queryParam.get("province"))){
                sql.append("and sbpb.mof_div_code= ? ");
                paramsList.add(queryParam.get("province"));
            }
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                sql.append(" and sbpb.is_send = ? ");
                paramsList.add(queryParam.get("flag").toString());
            }
            if (!StringUtils.isEmpty(queryParam.get("salaCardBank"))) {
                sql.append(" and pe.sala_card_bank = ? ");
                paramsList.add(queryParam.get("salaCardBank").toString());
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and sbpb.iden_no like concat( '%', ?, '%') ");
                paramsList.add(queryParam.get("idenNo").toString());
            }
        }
        sql.append(" order by ai.agency_code, sbpb.create_time");
        //String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        //Object[] params = this.boxUpParam(paramsList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(SalaryBankPayBack.class), paramsList.toArray());

    }

    @Override
    public List<SalaryBankPayBack> querySalaryBankPayBackTotal(PaginationDTO paginationDTO, Map<String, Object> queryParam,List<String> childrenId) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select distinct ai.agency_name, ")
                .append(" ai.id agency_id, ai.agency_code, sbpb.create_time ");
        sql.append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" JOIN ").append(TableName.PERSON_INFO).append(" pi ON sbpb.person_id = pi.id ")
                .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe on pi.id = pe.person_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai on pe.agency_id = ai.id ");
        sql.append(" where sbpb.bank_back_code = ?");
        paramsList.add(SystemDictConstant.FAILD);
        sql.append(" and ").append(SysUtils.inSql("ai.id", childrenId));
        if (!StringUtils.isEmpty(queryParam.get("province"))){
            sql.append(" and sbpb.mof_div_code = ?");
            paramsList.add(queryParam.get("province"));
        }
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                sql.append(" and sbpb.is_send = ? ");
                paramsList.add(queryParam.get("flag").toString());
            }
            if (!StringUtils.isEmpty(queryParam.get("salaCardBank"))) {
                sql.append(" and pe.sala_card_bank = ? ");
                paramsList.add(queryParam.get("salaCardBank").toString());
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and sbpb.iden_no like concat( '%', ?, '%') ");
                paramsList.add(queryParam.get("idenNo").toString());
            }
        }
        sql.append(" order by ai.agency_code, sbpb.create_time");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramsList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(SalaryBankPayBack.class), params);
    }

    @Override
    public int querySalaryBankPayBackTotal(Map<String, Object> queryParam) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" count(*) ");
        sql.append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" JOIN ").append(TableName.PERSON_INFO).append(" pi ON sbpb.person_id = pi.id ")
                .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe on pi.id = pe.person_id ");
//                .append(" join agency_info ai on pe.agency_id = ai.id ");
        sql.append(" where sbpb.bank_back_code = ?");
        paramsList.add(SystemDictConstant.FAILD);
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                sql.append(" and sbpb.is_send = ? ");
                paramsList.add(queryParam.get("flag").toString());
            }
            if (!StringUtils.isEmpty(queryParam.get("salaCardBank"))) {
                sql.append(" and pe.sala_card_bank = ? ");
                paramsList.add(queryParam.get("salaCardBank").toString());
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and sbpb.iden_no like concat( '%', ?, '%')  ");
                paramsList.add(queryParam.get("idenNo").toString());
            }
        }
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramsList.toArray());
    }

    @Override
    public void updateSalaryBankPayBack(SalaryBankPayBack salaryBankPayBack) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" set update_time = ?");
        paramsList.add(salaryBankPayBack.getUpdateTime());
        if (!StringUtils.isEmpty(salaryBankPayBack.getIsSend())) {
            sql.append(" , is_send = ? ");
            paramsList.add(salaryBankPayBack.getIsSend());
        }
        if (!StringUtils.isEmpty(salaryBankPayBack.getNewSalaCardNo())) {
            sql.append(" , new_sala_card_no = ? ");
            paramsList.add(salaryBankPayBack.getNewSalaCardNo());
        }
        if (!StringUtils.isEmpty(salaryBankPayBack.getIsUpdate())) {
            sql.append(" , is_update = ? ");
            paramsList.add(salaryBankPayBack.getIsUpdate());
        }
        if (!StringUtils.isEmpty(salaryBankPayBack.getVerifiedInAgencyCode())) {
            sql.append(" , verified_in_agency_code = ? ");
            paramsList.add(salaryBankPayBack.getVerifiedInAgencyCode());
        }
        if (!StringUtils.isEmpty(salaryBankPayBack.getVerifiedCode())) {
            sql.append(" , verified_code = ?" );
            paramsList.add(salaryBankPayBack.getVerifiedCode());
        }
        if (!StringUtils.isEmpty(salaryBankPayBack.getIsRecreate())) {
            sql.append(" , is_recreate = ? ");
            paramsList.add(salaryBankPayBack.getIsRecreate());
        }
        if (!StringUtils.isEmpty(salaryBankPayBack.getIsLongUse())) {
            sql.append(" , is_long_use = ? ");
            paramsList.add(salaryBankPayBack.getIsLongUse());
        }
        if (!StringUtils.isEmpty(salaryBankPayBack.getNewIdenNo())) {
            sql.append(" , new_iden_no = ? ");
            paramsList.add(salaryBankPayBack.getNewIdenNo());
        }
        if (!StringUtils.isEmpty(salaryBankPayBack.getNewPersonName())) {
            sql.append(" , new_person_name = ? ");
            paramsList.add(salaryBankPayBack.getNewPersonName());
        }
        if (!StringUtils.isEmpty(salaryBankPayBack.getNewSalaryBank())) {
            sql.append(" , new_salary_bank = ? ");
            paramsList.add(salaryBankPayBack.getNewSalaryBank());
        }
        if (!StringUtils.isEmpty(salaryBankPayBack.getPersonId())) {
            sql.append(" , person_id = ? ");
            paramsList.add(salaryBankPayBack.getPersonId());
        }
        sql.append(" where id = ?");
        paramsList.add(salaryBankPayBack.getId());
        this.getjdbcTemplate().update(sql.toString(), paramsList.toArray());
    }

    /**
     * 分发到单位
     *  根据单位id、送审状态、银行返回状态码、分发状态、创建时间》=当前月的第一天
     *  将单位下所有的人修改为已分发
     * @param agencyIds
     */
    @Override
    public void sendCardNoToAgency(List<String> agencyIds) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb ")

        .append(" set sbpb.update_time = ?, sbpb.is_send = ?, sbpb.verified_code = null ");
        sql.append(" where sbpb.bank_back_code = ?  and sbpb.is_send = ? ")
                .append(" and  ").append(SysUtils.inSql("sbpb.agency_id", agencyIds));
        paramsList.add(String.valueOf(DateUtils.getCurrentTimeStamp()));
        paramsList.add(SystemDictConstant.IS_TRUE);
        paramsList.add(SystemDictConstant.FAILD);

        paramsList.add(SystemDictConstant.IS_FALSE);
        this.getjdbcTemplate().update(sql.toString(), paramsList.toArray());
    }

    /**
     * 重新下发
     *  根据单位id、送审状态、银行返回状态码、分发状态、创建时间》=当前月的第一天
     *  将单位下所有的人修改为未分发，财政审核状态为null（未送审）
     * @param agencyIds
     */
    @Override
    public void cancelSendCard(List<String> agencyIds) {

        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb ")
                .append(" set sbpb.update_time = ?, sbpb.is_send = ?, sbpb.verified_code = null");
        sql.append(" where sbpb.bank_back_code = ?  and sbpb.is_send = ?  ")
                .append(" and  ").append(SysUtils.inSql("sbpb.agency_id", agencyIds));

        paramsList.add(String.valueOf(DateUtils.getCurrentTimeStamp()));
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add(SystemDictConstant.FAILD);

        paramsList.add(SystemDictConstant.IS_TRUE);
        this.getjdbcTemplate().update(sql.toString(), paramsList.toArray());
    }

    @Override
    public List<SalaryBankPayBack> querySalaryBankPayBackSendPage(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" sbpb.id, sbpb.biz_key, sbpb.create_time, sbpb.update_time, sbpb.iden_no, sbpb.person_name,sbpb.agency_id, ")
                .append(" sbpb.old_sala_card_no, sbpb.bank_back_code, sbpb.bank_back_remark, ")
                .append(" sbpb.operate_code, sbpb.is_update, sbpb.verified_in_agency_code, sbpb.verified_code, ")
                .append(" sbpb.is_recreate, sbpb.is_long_use, sbpb.is_send, pi.sex_code, pe.sala_card_bank, ai.agency_name, ")
                .append(" case when sbpb.new_sala_card_no='' then sbpb.old_sala_card_no else sbpb.new_sala_card_no end new_sala_card_no,")
                .append(" case when sbpb.new_salary_bank='' then pe.sala_card_bank else sbpb.new_salary_bank end new_salary_bank,")
                .append(" case when sbpb.new_person_name='' then pi.person_name else sbpb.new_person_name end new_person_name,")
                .append(" case when sbpb.new_iden_no='' then sbpb.iden_no else sbpb.new_iden_no end new_iden_no,pe.person_id ");
        sql.append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" JOIN ").append(TableName.PERSON_INFO).append(" pi ON sbpb.person_id = pi.id ")
                .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe on pi.id = pe.person_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai on pe.agency_id = ai.id ");
        sql.append(" where sbpb.bank_back_code = ? and sbpb.is_send = ?");
        sql.append(" and pe.agency_id = ? ");
        paramsList.add(SystemDictConstant.FAILD);
        paramsList.add(SystemDictConstant.IS_TRUE);
        paramsList.add(queryParam.get("agencyId"));
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("province"))){
                sql.append(" and sbpb.mof_div_code = ?");
                paramsList.add(queryParam.get("province"));
            }
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                String flag = queryParam.get("flag").toString();
                // 待修改
                if ("waitUpdate".equals(flag)) {
//                    sql.append(" and( sbpb.is_update = ?");
//                    paramsList.add(SystemDictConstant.IS_FALSE);
//
//                    sql.append(" or sbpb.verified_in_agency_code = ?");
//                    paramsList.add(SystemDictConstant.IS_SUBMIT);
//
//                    sql.append("  or sbpb.verified_in_agency_code = ? )");
//                    paramsList.add(SystemDictConstant.IS_AUDIT);
                    sql.append("  and sbpb.verified_code is null");
                }
//                // 待审核
//                else if ("waitAudit".equals(flag)) {
//
//                }
//                // 已审核
//                else if ("audit".equals(flag)) {
//
//                }
                // 已送审
                else if ("submit".equals(flag)) {
                    sql.append("  and sbpb.verified_code = ?");
                    paramsList.add(SystemDictConstant.IS_SUBMIT);
                }
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and sbpb.iden_no like concat( '%', ?, '%') ");
                paramsList.add(queryParam.get("idenNo").toString());
            }
        }
        sql.append(" order by sbpb.is_update");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramsList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(SalaryBankPayBack.class), params);
    }

    @Override
    public int querySalaryBankPayBackSendTotal(Map<String, Object> queryParam) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" count(*) ");
        sql.append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" JOIN ").append(TableName.PERSON_INFO).append(" pi ON sbpb.person_id = pi.id ")
                .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe on pi.id = pe.person_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai on pe.agency_id = ai.id ");
        sql.append(" where sbpb.bank_back_code = ? and sbpb.is_send = ?");
        sql.append(" and pe.agency_id = ? ");
        paramsList.add(SystemDictConstant.FAILD);
        paramsList.add(SystemDictConstant.IS_TRUE);
        paramsList.add(queryParam.get("agencyId"));
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("province"))){
                sql.append(" and sbpb.mof_div_code = ?");
                paramsList.add(queryParam.get("province"));
            }
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                String flag = queryParam.get("flag").toString();
                // 待修改
                if ("waitUpdate".equals(flag)) {
//                    sql.append(" and( sbpb.is_update = ?");
//                    paramsList.add(SystemDictConstant.IS_FALSE);
//
//                    sql.append(" or sbpb.verified_in_agency_code = ?");
//                    paramsList.add(SystemDictConstant.IS_SUBMIT);
//
//                    sql.append("  or sbpb.verified_in_agency_code = ? )");
//                    paramsList.add(SystemDictConstant.IS_AUDIT);

                    sql.append("  and sbpb.verified_code is null");
                }
//                // 待审核
//                else if ("waitAudit".equals(flag)) {
//                    sql.append(" and sbpb.verified_in_agency_code = ?");
//                    paramsList.add(SystemDictConstant.IS_SUBMIT);
//                }
//                // 已审核
//                else if ("audit".equals(flag)) {
//                    sql.append("  and sbpb.verified_in_agency_code = ?");
//                    paramsList.add(SystemDictConstant.IS_AUDIT);
//                }
                // 已送审
                else if ("submit".equals(flag)) {
                    sql.append("  and sbpb.verified_code = ?");
                    paramsList.add(SystemDictConstant.IS_SUBMIT);
                }
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and sbpb.iden_no like concat( '%', ?, '%') ");
                paramsList.add(queryParam.get("idenNo").toString());
            }
        }
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramsList.toArray());
    }

    @Override
    public void revokeUpdate(SalaryBankPayBack salaryBankPayBack) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" set ");
        sql.append(" new_sala_card_no = null, is_update = ?, verified_in_agency_code = null, ")
                .append(" is_long_use = ?, update_time = ?");
        sql.append(" where id = ?");
        this.getjdbcTemplate().update(sql.toString(),
                new Object[]{salaryBankPayBack.getIsUpdate(), salaryBankPayBack.getIsLongUse(),
                salaryBankPayBack.getUpdateTime(), salaryBankPayBack.getId()});
    }

    /**
     * 获取账户异常，未修改的人员数量
     * @param agencyId
     * @return
     */
    @Override
    public int getNotUpdateTotal(String agencyId) {

        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pi ")
                .append(" on sbpb.person_id = pi.id ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pe ")
                .append(" on pi.id = pe.person_id ");;
        sql.append(" where pe.agency_id = ? and sbpb.is_update = ? and sbpb.bank_back_code = ? ");
        return this.getjdbcTemplate().queryForObject(sql.toString(), Integer.class,
                new Object[]{agencyId, SystemDictConstant.IS_FALSE,
                SystemDictConstant.FAILD});
    }

    /**
     * 修改单位审核状态为审核通过/撤销审核
     *  根据 单位id、银行返回状态、未提交到财政、创建时间>=当月的第一天
     * @param salaryBankPayBack
     */
    @Override
    public void updateVerifyByAgencyId(SalaryBankPayBack salaryBankPayBack) {

        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb ")
                .append(" set sbpb.update_time = ?");
        paramsList.add(salaryBankPayBack.getUpdateTime());
        if (!StringUtils.isEmpty(salaryBankPayBack.getVerifiedInAgencyCode())) {
            sql.append(" , sbpb.verified_in_agency_code = ? ");
            paramsList.add(salaryBankPayBack.getVerifiedInAgencyCode());
        }
        sql.append(" where  ")
            .append("  sbpb.bank_back_code = ? ")

                // 未提交到财政
            .append(" and sbpb.verified_code is null")
                .append(" and sbpb.agency_id = ? ");

        // 账户异常
        paramsList.add(SystemDictConstant.FAILD);
        // 单位id
        paramsList.add(salaryBankPayBack.getAgencyId());
        this.getjdbcTemplate().update(sql.toString(), paramsList.toArray());
    }

    /**
     * 财政送审
     * @param salaryBankPayBack
     */
    @Override
    public void sendVerifyFinance(SalaryBankPayBack salaryBankPayBack) {

        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb ");

        sql.append(" set sbpb.update_time = ?");
        paramsList.add(salaryBankPayBack.getUpdateTime());
        if (!StringUtils.isEmpty(salaryBankPayBack.getVerifiedCode())) {
            sql.append(" , sbpb.verified_code = ? ");
            paramsList.add(salaryBankPayBack.getVerifiedCode());
        }
        sql.append(" where ")
                .append("  sbpb.bank_back_code = ?")
                .append(" and sbpb.verified_in_agency_code = ?")
                // 未送审财政
                .append(" and sbpb.verified_code is null ")
                .append(" and sbpb.agency_id = ? ");


        // 账户异常
        paramsList.add(SystemDictConstant.FAILD);
        // 单位审核通过
        paramsList.add(SystemDictConstant.IS_AUDIT);
        //paramsList.add(firstDate);
        // 单位id
        paramsList.add(salaryBankPayBack.getAgencyId());
        this.getjdbcTemplate().update(sql.toString(), paramsList.toArray());
    }

    /**
     * 撤销财政送审
     * @param salaryBankPayBack
     */
    @Override
    public void revokeVerifyFinance(SalaryBankPayBack salaryBankPayBack) {
        String firstDate = LocalDateTime.now().with(TemporalAdjusters.firstDayOfMonth()).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pi ")
                .append(" on sbpb.person_id = pi.id ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pe ")
                .append(" on pi.id = pe.person_id ");
        sql.append(" set sbpb.update_time = ?");
        sql.append(" , sbpb.verified_code = ? ");
        sql.append(" where pe.agency_id = ?")
                .append(" and sbpb.bank_back_code = ? ")
                .append(" and sbpb.verified_in_agency_code = ?")
                .append(" and sbpb.create_time >= ? ")
                // 财政未送审，或者未确认
                .append(" and ( sbpb.verified_code is null or sbpb.verified_code = ?)");
        paramsList.add(salaryBankPayBack.getUpdateTime());
        paramsList.add(salaryBankPayBack.getVerifiedCode());
        // 单位id
        paramsList.add(salaryBankPayBack.getAgencyId());
        // 账户异常
        paramsList.add(SystemDictConstant.FAILD);
        // 单位审核通过
        paramsList.add(SystemDictConstant.IS_AUDIT);
        paramsList.add(firstDate);
        // 财政未确认
        paramsList.add(SystemDictConstant.IS_SUBMIT);
        this.getjdbcTemplate().update(sql.toString(), paramsList.toArray());
    }

    @Override
    public List<SalaryBankPayBack> getAgencyVerifyState(List<String> agencyIds) {
        String firstDate = LocalDateTime.now().with(TemporalAdjusters.firstDayOfMonth()).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" sbpb.id, sbpb.biz_key, sbpb.create_time, sbpb.update_time, sbpb.iden_no, pi.person_name, ")
                .append(" sbpb.old_sala_card_no, sbpb.new_sala_card_no, sbpb.bank_back_code, sbpb.bank_back_remark, ")
                .append(" sbpb.operate_code, sbpb.is_update, sbpb.verified_in_agency_code, sbpb.verified_code, ")
                .append(" sbpb.is_recreate, sbpb.is_long_use, sbpb.is_send, pi.sex_code, pe.sala_card_bank, ai.agency_name, ")
                .append(" ai.id agency_id, ai.agency_code ");
        sql.append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" JOIN ").append(TableName.PERSON_INFO).append(" pi ON sbpb.person_id = pi.id ")
                .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe on pi.id = pe.person_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai on pe.agency_id = ai.id ");
        sql.append(" where  bank_back_code='F' and ").append(SysUtils.inSql("ai.id", agencyIds));
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(SalaryBankPayBack.class));
    }

    @Override
    public List<SalaryBankPayBack> queryFinanceVerifyPage(Map<String, Object> queryParam,List<String> childrenId) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" sbpb.id, sbpb.biz_key, sbpb.create_time, sbpb.update_time, sbpb.iden_no, sbpb.person_name, ")
                .append(" sbpb.old_sala_card_no, sbpb.bank_back_code, sbpb.bank_back_remark, ")
                .append(" sbpb.operate_code, sbpb.is_update, sbpb.verified_in_agency_code, sbpb.verified_code, ")
                .append(" sbpb.is_recreate, sbpb.is_long_use, sbpb.is_send, pi.sex_code, pe.sala_card_bank, ai.agency_name, ")
                .append(" ai.id agency_id, ai.agency_code, ")
                .append(" case when sbpb.new_sala_card_no='' then sbpb.old_sala_card_no else sbpb.new_sala_card_no end new_sala_card_no,")
                .append(" case when sbpb.new_salary_bank='' then pe.sala_card_bank else sbpb.new_salary_bank end new_salary_bank,")
                .append(" case when sbpb.new_person_name='' then pi.person_name else sbpb.new_person_name end new_person_name,")
                .append(" case when sbpb.new_iden_no='' then sbpb.iden_no else sbpb.new_iden_no end new_iden_no,pe.person_id ");
        sql.append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" JOIN ").append(TableName.PERSON_INFO).append(" pi ON sbpb.person_id = pi.id ")
                .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe on pi.id = pe.person_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai on pe.agency_id = ai.id ");
        sql.append(" where sbpb.bank_back_code = ? and verified_in_agency_code=?");
        paramsList.add(SystemDictConstant.FAILD);
        paramsList.add(SystemDictConstant.IS_AUDIT);
        sql.append(" and ").append(SysUtils.inSql("ai.id", childrenId));
        if (!StringUtils.isEmpty(queryParam.get("province"))){
            sql.append(" and sbpb.mof_div_code = ?");
            paramsList.add(queryParam.get("province"));
        }
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                if(!"recreated".equals(queryParam.get("flag"))){
                    sql.append(" and sbpb.verified_code = ? and is_recreate=0");
                    paramsList.add(queryParam.get("flag").toString());
                }else{
                    sql.append(" and is_recreate=1 ");
                }

            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and sbpb.iden_no like concat( '%', ?, '%') ");
                paramsList.add(queryParam.get("idenNo").toString());
            }
        }
        sql.append(" order by ai.agency_code, sbpb.create_time");
        //String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        //Object[] params = this.boxUpParam(paramsList.toArray(), paginationDTO);

        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(SalaryBankPayBack.class), paramsList.toArray());
    }

    @Override
    public List<SalaryBankPayBack> queryFinanceVerifyTotal(PaginationDTO paginationDTO, Map<String, Object> queryParam,List<String> childrenId) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select distinct ai.agency_name,ai.id agency_id, ai.agency_code, sbpb.create_time ");

        sql.append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" JOIN ").append(TableName.PERSON_INFO).append(" pi ON sbpb.person_id = pi.id ")
                .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe on pi.id = pe.person_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai on pe.agency_id = ai.id ");
        sql.append(" where sbpb.bank_back_code = ? and verified_in_agency_code=?");
        paramsList.add(SystemDictConstant.FAILD);
        paramsList.add(SystemDictConstant.IS_AUDIT);
        sql.append(" and ").append(SysUtils.inSql("ai.id", childrenId));
        if (!StringUtils.isEmpty(queryParam.get("province"))){
            sql.append(" and sbpb.mof_div_code = ?");
            paramsList.add(queryParam.get("province"));
        }
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                if(!"recreated".equals(queryParam.get("flag"))){
                    sql.append(" and sbpb.verified_code = ? and is_recreate=0");
                    paramsList.add(queryParam.get("flag").toString());
                }else{
                    sql.append(" and is_recreate=1 ");
                }
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and sbpb.iden_no like concat( '%', ?, '%') ");
                paramsList.add(queryParam.get("idenNo").toString());
            }
        }
        sql.append(" order by ai.agency_code, sbpb.create_time");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramsList.toArray(), paginationDTO);

        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(SalaryBankPayBack.class),params);
    }

    /**
     * 财政审核卡号 财政确认
     * @param salaryBankPayBack
     */
    @Override
    public void financeVerifyConfirm(SalaryBankPayBack salaryBankPayBack) {
        //String firstDate = LocalDateTime.now().with(TemporalAdjusters.firstDayOfMonth()).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb ")

        .append(" set sbpb.update_time = ? , verified_code=? ");
        paramsList.add(salaryBankPayBack.getUpdateTime());
        paramsList.add(SystemDictConstant.IS_SUBMIT);
        sql.append(" where ")
                .append(" sbpb.bank_back_code = ?")
                .append(" and sbpb.verified_in_agency_code = ?")
                .append(" and sbpb.agency_id = ? ");



        // 账户异常
        paramsList.add(SystemDictConstant.FAILD);
        // 单位审核通过
        paramsList.add(SystemDictConstant.IS_AUDIT);
        // 单位id
        paramsList.add(salaryBankPayBack.getAgencyId());
        this.getjdbcTemplate().update(sql.toString(), paramsList.toArray());
    }

    /**
     * 财政审核卡号 财政退回
     * @param salaryBankPayBack
     */
    @Override
    public void financeVerifySendBack(SalaryBankPayBack salaryBankPayBack) {

        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb ")
        .append(" set sbpb.update_time = ? , verified_code=? ");
        paramsList.add(salaryBankPayBack.getUpdateTime());
        paramsList.add(null);
        sql.append(" where ")
                .append(" sbpb.bank_back_code = ?")
                .append(" and sbpb.verified_in_agency_code = ?")
                .append(" and sbpb.agency_id = ? ");



        // 账户异常
        paramsList.add(SystemDictConstant.FAILD);
        // 单位审核通过
        paramsList.add(SystemDictConstant.IS_AUDIT);
        // 单位id
        paramsList.add(salaryBankPayBack.getAgencyId());
        this.getjdbcTemplate().update(sql.toString(), paramsList.toArray());
    }

    /**
     * 财政审核卡号 取消确认
     * @param salaryBankPayBack
     */
    @Override
    public void financeVerifyCancelConfirm(SalaryBankPayBack salaryBankPayBack) {
        //String firstDate = LocalDateTime.now().with(TemporalAdjusters.firstDayOfMonth()).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb ");
        sql.append(" set sbpb.update_time = ? , verified_code=? ");
        paramsList.add(salaryBankPayBack.getUpdateTime());
        paramsList.add(SystemDictConstant.NO_SUBMIT);
        sql.append(" where ")
                .append(" sbpb.bank_back_code = ?")
                .append(" and sbpb.verified_in_agency_code = ?")
                .append(" and sbpb.agency_id = ? ");




        // 账户异常
        paramsList.add(SystemDictConstant.FAILD);
        // 单位审核通过
        paramsList.add(SystemDictConstant.IS_AUDIT);
        // 单位id
        paramsList.add(salaryBankPayBack.getAgencyId());
        this.getjdbcTemplate().update(sql.toString(), paramsList.toArray());
    }

    /**
     * 财政审核卡号 审核通过
     * @param salaryBankPayBack
     */
    @Override
    public void financeVerifyAuditPass(SalaryBankPayBack salaryBankPayBack) {
       // String firstDate = LocalDateTime.now().with(TemporalAdjusters.firstDayOfMonth()).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb ")

        .append(" set sbpb.update_time = ? , verified_code=? ");
        paramsList.add(salaryBankPayBack.getUpdateTime());
        paramsList.add(SystemDictConstant.IS_AUDIT);
        sql.append(" where ")
                .append(" sbpb.bank_back_code = ?")
                .append(" and sbpb.verified_in_agency_code = ?")
                .append(" and sbpb.agency_id = ? ");




        // 账户异常
        paramsList.add(SystemDictConstant.FAILD);
        // 单位审核通过
        paramsList.add(SystemDictConstant.IS_AUDIT);
        // 单位id
        paramsList.add(salaryBankPayBack.getAgencyId());
        this.getjdbcTemplate().update(sql.toString(), paramsList.toArray());

    }

    @Override
    public void financeVerifyUndoAudit(SalaryBankPayBack salaryBankPayBack) {

        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb ")
               .append(" set sbpb.update_time = ? , verified_code=? ");
        paramsList.add(salaryBankPayBack.getUpdateTime());
        paramsList.add(SystemDictConstant.IS_SUBMIT);
        sql.append(" where ")
                .append(" sbpb.bank_back_code = ?")
                .append(" and sbpb.verified_in_agency_code = ?")
                .append(" and sbpb.agency_id = ?");



        // 账户异常
        paramsList.add(SystemDictConstant.FAILD);
        // 单位审核通过
        paramsList.add(SystemDictConstant.IS_AUDIT);

        // 单位id
        paramsList.add(salaryBankPayBack.getAgencyId());
        this.getjdbcTemplate().update(sql.toString(), paramsList.toArray());

    }
    /**
     * 卡号财政处理 分页查询所有
     * @param paginationDTO
     * @param queryParam
     * @return
     */
    @Override
    public List<SalaryBankPayBack> queryCardNumberFiscalPage(PaginationDTO paginationDTO, Map<String, Object> queryParam,List<String> childrenId) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" sbpb.id, sbpb.iden_no, pi.person_name, sbpb.old_sala_card_no, sbpb.new_sala_card_no, ")
                .append(" sbpb.bank_back_code, sbpb.bank_back_remark, sbpb.operate_code, sbpb.is_update, ")
                .append(" sbpb.verified_in_agency_code, sbpb.verified_code, sbpb.is_recreate, sbpb.is_long_use, ")
                .append(" sbpb.is_send, pi.sex_code, pe.sala_card_bank, ai.agency_name, ")
                .append(" ai.id agency_id, ai.agency_code, sum(sme.s1) sfgzze, sum(sme.s2) yfgzze, ")
                .append(" pi.sex_code, pi.nat_code, pi.state, pe.sch_rec_code, ")
                .append(" pe.pos_code, pe.gr_code, pe.is_auth, pe.sala_gr_code, ")
                .append(" pi.birthday, pe.enter_agency_date, pe.retire_date, pi.work_init_date, ")
                .append(" pe.end_date, pe.dead_date, pe.remarks ");
        sql.append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" join ").append(TableName.PERSON_INFO).append(" pi ON sbpb.person_id = pi.id ")
                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" pe on pi.id = pe.person_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai on pe.agency_id = ai.id ")
                .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" sme on sme.person_id=pi.id ");
        sql.append(" where sbpb.bank_back_code = ? and verified_code = ? ");
        paramsList.add(SystemDictConstant.FAILD);
        paramsList.add(SystemDictConstant.IS_AUDIT);
        sql.append(" and ").append(SysUtils.inSql("ai.id", childrenId));
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                if("0".equals(queryParam.get("flag"))){
//                    未生成银行数据，未更新人员信息
                    sql.append(" and ( sbpb.is_recreate = 0 or sbpb.is_update_person = 0 ) ");
                }else if("1".equals(queryParam.get("flag"))){
//                    已生成银行数据
                    sql.append(" and sbpb.is_recreate = 1 ");
                }else if("2".equals(queryParam.get("flag"))){
//                    已更新人员信息
                    sql.append(" and sbpb.is_update_person = 1 ");
                }
            }
            if (!StringUtils.isEmpty(queryParam.get("province"))){
                sql.append(" and sbpb.mof_div_code = ?");
                paramsList.add(queryParam.get("province"));
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))){
                sql.append(" and sbpb.iden_no like concat( '%', ?, '%') ");
                paramsList.add(queryParam.get("idenNo").toString());
            }
            if(!StringUtils.isEmpty(queryParam.get("salaCardBank"))){
                sql.append(" and  pe.sala_card_bank = ? ");
                paramsList.add(queryParam.get("salaCardBank"));
            }
            if(!StringUtils.isEmpty(queryParam.get("isLongUse"))){
                sql.append(" and sbpb.is_long_use = ? ");
                paramsList.add(queryParam.get("isLongUse"));
            }
        }
        sql.append(" group by ");
        sql.append("sbpb.id, ");
        sql.append("sbpb.iden_no, ");
        sql.append("pi.person_name, ");
        sql.append("sbpb.old_sala_card_no, ");
        sql.append("sbpb.new_sala_card_no, ");
        sql.append("sbpb.bank_back_code, ");
        sql.append("sbpb.bank_back_remark, ");
        sql.append("sbpb.operate_code, ");
        sql.append("sbpb.is_update, ");
        sql.append("sbpb.verified_in_agency_code, ");
        sql.append("sbpb.verified_code, ");
        sql.append("sbpb.is_recreate, ");
        sql.append("sbpb.is_long_use, ");
        sql.append("sbpb.is_send, ");
        sql.append("pi.sex_code, ");
        sql.append("pe.sala_card_bank, ");
        sql.append("ai.agency_name, ");
        sql.append("ai.id, ");
        sql.append("ai.agency_code, ");
        sql.append("pi.sex_code, ");
        sql.append("pi.nat_code, ");
        sql.append("pi.state, ");
        sql.append("pe.sch_rec_code, ");
        sql.append("pe.pos_code, ");
        sql.append("pe.gr_code, ");
        sql.append("pe.is_auth, ");
        sql.append("pe.sala_gr_code, ");
        sql.append("pi.birthday, ");
        sql.append("pe.enter_agency_date, ");
        sql.append("pe.retire_date, ");
        sql.append("pi.work_init_date, ");
        sql.append("pe.end_date, ");
        sql.append("pe.dead_date, ");
        sql.append("pe.remarks ");
        sql.append(" order by ai.agency_code, sbpb.create_time");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramsList.toArray(), paginationDTO);
         return super.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper<>(SalaryBankPayBack.class), params);
    }

    @Override
    public int getCardNumberFiscalNum(Map<String, Object> queryParam, List<String> childrenId) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) from  (select ");
        sql.append(" sbpb.id, sbpb.biz_key, sbpb.create_time, sbpb.update_time, sbpb.iden_no, pi.person_name, ")
                .append(" sbpb.old_sala_card_no, sbpb.new_sala_card_no, sbpb.bank_back_code, sbpb.bank_back_remark, ")
                .append(" sbpb.operate_code, sbpb.is_update, sbpb.verified_in_agency_code, sbpb.verified_code, ")
                .append(" sbpb.is_recreate, sbpb.is_long_use, sbpb.is_send, pi.sex_code, pe.sala_card_bank, ai.agency_name, ")
                .append(" ai.id agency_id, ai.agency_code, ")
                .append(" sum(sme.s1) sfgzze, sum(sme.s2) yfgzze");
        sql.append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" JOIN ").append(TableName.PERSON_INFO).append(" pi ON sbpb.person_id = pi.id ")
                .append(" JOIN ").append(TableName.PERSON_EXT).append(" pe on pi.id = pe.person_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai on pe.agency_id = ai.id ")
                .append(" join ").append(TableName.SALARY_MONTH_END).append(" sme on sme.person_id=pi.id ");
        sql.append(" where sbpb.bank_back_code = ? and verified_code=?");
        paramsList.add(SystemDictConstant.FAILD);
        paramsList.add(SystemDictConstant.IS_AUDIT);
        sql.append(" and ").append(SysUtils.inSql("ai.id", childrenId));
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                if("0".equals(queryParam.get("flag"))){
//                    未生成银行数据，未更新人员信息
                    sql.append(" and ( sbpb.is_recreate = 0 or sbpb.is_update_person = 0 ) ");
                }else if("1".equals(queryParam.get("flag"))){
//                    已生成银行数据
                    sql.append(" and sbpb.is_recreate = 1 ");
                }else if("2".equals(queryParam.get("flag"))){
//                    已更新人员信息
                    sql.append(" and sbpb.is_update_person = 1 ");
                }
            }
            if (!StringUtils.isEmpty(queryParam.get("province"))){
                sql.append(" and sbpb.mof_div_code = ?");
                paramsList.add(queryParam.get("province"));
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and sbpb.iden_no like concat( '%', ?, '%') ");
                paramsList.add(queryParam.get("idenNo").toString());
            }
            if(!StringUtils.isEmpty(queryParam.get("salaCardBank"))){
                sql.append(" and  pe.sala_card_bank = ? ");
                paramsList.add(queryParam.get("salaCardBank"));
            }
            if(!StringUtils.isEmpty(queryParam.get("isLongUse"))){
                sql.append(" and sbpb.is_long_use = ? ");
                paramsList.add(queryParam.get("isLongUse"));
            }
        }
        sql.append(" group by ");
        sql.append("sbpb.id, ");
        sql.append("sbpb.biz_key, ");
        sql.append("sbpb.create_time, ");
        sql.append("sbpb.update_time, ");
        sql.append("sbpb.iden_no, ");
        sql.append("pi.person_name, ");
        sql.append("sbpb.old_sala_card_no, ");
        sql.append("sbpb.new_sala_card_no, ");
        sql.append("sbpb.bank_back_code, ");
        sql.append("sbpb.bank_back_remark, ");
        sql.append("sbpb.operate_code, ");
        sql.append("sbpb.is_update, ");
        sql.append("sbpb.verified_in_agency_code, ");
        sql.append("sbpb.verified_code, ");
        sql.append("sbpb.is_recreate, ");
        sql.append("sbpb.is_long_use, ");
        sql.append("sbpb.is_send, ");
        sql.append("pi.sex_code, ");
        sql.append("pe.sala_card_bank, ");
        sql.append("ai.agency_name, ");
        sql.append("ai.id, ");
        sql.append("ai.agency_code ");
        sql.append(") a ");

        return this.getjdbcTemplate().queryForObject(sql.toString(), Integer.class, paramsList.toArray());
    }

    @Override
    public SalaryBankPayBack getCardNumberFiscalCombined(Map<String, Object> queryParam,List<String> childrenId) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" sum(sme.s1) sfgzze,sum(sme.s2) yfgzze");
        sql.append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" join ").append(TableName.PERSON_INFO).append(" pi ON sbpb.person_id = pi.id ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pe on pi.id = pe.person_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai on pe.agency_id = ai.id ")
                .append(" join ").append(TableName.SALARY_MONTH_END).append(" sme on sme.person_id=pi.id ");
        sql.append(" where sbpb.bank_back_code = ? and verified_code=?");
        paramsList.add(SystemDictConstant.FAILD);
        paramsList.add(SystemDictConstant.IS_AUDIT);
        sql.append(" and ").append(SysUtils.inSql("ai.id", childrenId));
        if (!StringUtils.isEmpty(queryParam)) {
            if("0".equals(queryParam.get("flag"))){
//                    未生成银行数据，未更新人员信息
                sql.append(" and ( sbpb.is_recreate = 0 or sbpb.is_update_person = 0 ) ");
            }else if("1".equals(queryParam.get("flag"))){
//                    已生成银行数据
                sql.append(" and sbpb.is_recreate = 1 ");
            }else if("2".equals(queryParam.get("flag"))){
//                    已更新人员信息
                sql.append(" and sbpb.is_update_person = 1 ");
            }
            if (!StringUtils.isEmpty(queryParam.get("province"))){
                sql.append(" and sbpb.mof_div_code = ?");
                paramsList.add(queryParam.get("province"));
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and sbpb.iden_no like concat( '%', ?, '%') ");
                paramsList.add(queryParam.get("idenNo").toString());
            }
            if(!StringUtils.isEmpty(queryParam.get("salaCardBank"))){
                sql.append(" and  pe.sala_card_bank = ? ");
                paramsList.add(queryParam.get("salaCardBank"));
            }
            if(!StringUtils.isEmpty(queryParam.get("isLongUse"))){
                sql.append(" and sbpb.is_long_use = ? ");
                paramsList.add(queryParam.get("isLongUse"));
            }
        }
        try {
            return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(SalaryBankPayBack.class),paramsList.toArray());
        } catch (DataAccessException e) {
           SalaryBankPayBack salaryBankPayBack=new SalaryBankPayBack();
           salaryBankPayBack.setYfgzze(0);
           salaryBankPayBack.setSfgzze(0);
           return salaryBankPayBack;
        }
    }

    @Override
    public List<SalaryBankPayBack> getisLongUse() {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" sbpb.id, sbpb.iden_no, pi.person_name, sbpb.old_sala_card_no, sbpb.new_sala_card_no, ")
                .append(" sbpb.bank_back_code, sbpb.bank_back_remark, sbpb.operate_code, sbpb.is_update, ")
                .append(" sbpb.verified_in_agency_code, sbpb.verified_code, sbpb.is_recreate, sbpb.is_update_person, sbpb.is_long_use, ")
                .append(" sbpb.is_send, pi.sex_code, pe.sala_card_bank, ai.agency_name, ")
                .append(" ai.id agency_id, ai.agency_code, sum(sme.s1) sfgzze, sum(sme.s2) yfgzze, ")
                .append(" pi.sex_code, pi.nat_code, pi.state, pe.sch_rec_code, ")
                .append(" pe.pos_code, pe.gr_code, pe.is_auth, pe.sala_gr_code, ")
                .append(" pi.birthday, pe.enter_agency_date, pe.retire_date, pi.work_init_date, ")
                .append(" pe.end_date, pe.dead_date, pe.remarks ")
                .append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" join ").append(TableName.PERSON_INFO).append(" pi ").append(" on sbpb.person_id = pi.id ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pe ").append(" on pi.id = pe.person_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" ai ").append(" on pe.agency_id = ai.id ")
                .append(" join ").append(TableName.SALARY_MONTH_END).append(" sme ").append(" on sme.person_id = pi.id ");
        sql.append(" where ").append(" sbpb.bank_back_code = ? ");
        paramsList.add(SystemDictConstant.FAILD);
        sql.append(" and verified_code = ? ");
        paramsList.add(SystemDictConstant.IS_AUDIT);
        sql.append(" and sbpb.is_long_use = 1 and sbpb.is_update_person = 0 ");
        sql.append(" group by pi.id ");
        sql.append(" order by ai.agency_code, sbpb.create_time");
        return super.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryBankPayBack.class), paramsList.toArray());
    }

    @Override
    public void updateIsLongUse(SalaryBankPayBack salaryBankPayBack) {
        String firstDate = LocalDateTime.now().with(TemporalAdjusters.firstDayOfMonth()).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pi ")
                .append(" on sbpb.person_id = pi.id ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pe ")
                .append(" on pi.id = pe.person_id ");
        sql.append(" set sbpb.update_time = ?, sbpb.is_update_person = ?  ");
        paramsList.add(salaryBankPayBack.getUpdateTime());
        paramsList.add(SystemDictConstant.IS_TRUE);
        sql.append("where sbpb.bank_back_code = ?")
                .append(" and sbpb.verified_code = ?")
                .append(" and sbpb.create_time >= ? ")
                .append(" and sbpb.is_long_use = ? ")
                .append(" and pi.iden_no = ? ");
        // 账户异常
        paramsList.add(SystemDictConstant.FAILD);
        // 财政审核考好通过
        paramsList.add(SystemDictConstant.IS_AUDIT);
        paramsList.add(firstDate);
        //长期使用
        paramsList.add(salaryBankPayBack.getIsLongUse());
        //身份证号
        paramsList.add(salaryBankPayBack.getIdenNo());
        this.getjdbcTemplate().update(sql.toString(), paramsList.toArray());

    }

    @Override
    public void undoUpdateIsLongUse(SalaryBankPayBack salaryBankPayBack) {
        String firstDate = LocalDateTime.now().with(TemporalAdjusters.firstDayOfMonth()).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pi ")
                .append(" on sbpb.person_id = pi.id ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pe ")
                .append(" on pi.id = pe.person_id ");
        sql.append(" set sbpb.update_time = ?, sbpb.is_update_person`= ? ");
        paramsList.add(salaryBankPayBack.getUpdateTime());
        paramsList.add(SystemDictConstant.IS_FALSE);
        sql.append("where sbpb.bank_back_code = ?")
                .append(" and sbpb.verified_code = ?")
                .append(" and sbpb.create_time >= ? ")
                .append(" and sbpb.is_long_use= ? ")
                .append(" and pi.iden_no= ? ");

        // 账户异常
        paramsList.add(SystemDictConstant.FAILD);
        // 财政审核考好通过
        paramsList.add(SystemDictConstant.IS_AUDIT);
        paramsList.add(firstDate);
        //长期使用
        paramsList.add(salaryBankPayBack.getIsLongUse());
        //身份证号
        paramsList.add(salaryBankPayBack.getIdenNo());
        this.getjdbcTemplate().update(sql.toString(), paramsList.toArray());
    }

    @Override
    public List<Map<String, Object>> selectIdAndPersonIdByIsUpdate() {
        StringBuilder sb = new StringBuilder();
        sb.append(" select sbpbk.id, sbpbk.person_id personId, sbpbk.iden_no idenNo ")
                .append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpbk ")
                .append(" where ").append(" sbpbk.is_update = 1 and sbpbk.is_recreate = 0 and sbpbk.verified_code = 2 ");
        return super.getjdbcTemplate().queryForList(sb.toString());
    }

    @Override
    public List<Map<String, Object>> selectIdAndPersonIdByIsUpdate(List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select sbpbk.id, sbpbk.person_id personId, sbpbk.iden_no idenNo ")
                .append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpbk ")
                .append(" where ").append(" sbpbk.is_update = 1 and sbpbk.is_recreate = 0 and sbpbk.verified_code = 2 ")
                .append(" and sbpbk.agency_id in ( ");
        for(String id : agencyIds){
            if(agencyIds.indexOf(id) != agencyIds.size() - 1){
                sb.append(" ?, ");
            }else{
                sb.append(" ?) ");
            }
        }
        return super.getjdbcTemplate().queryForList(sb.toString(), agencyIds.toArray());
    }






    @Override
    public void batchUpdateIsRecreateByIds(List<String> ids) throws AppRuntimeException {
        if(CollectionUtils.isEmpty(ids)){
            throw new AppRuntimeException("没有需要生成的数据。");
        }
        StringBuilder sb = new StringBuilder();
        sb.append(" update ").append(TableName.SALARY_BANK_PAY_BACK)
                .append(" set ").append(" is_recreate = 1 ")
                .append(" where ").append(" id in ( ");
        for(String id : ids){
            if(ids.indexOf(id) != ids.size() - 1){
                sb.append(" ?, ");
            }else{
                sb.append(" ?) ");
            }
        }
        super.getjdbcTemplate().update(sb.toString(), ids.toArray());
    }

    @Override
    public List<Offer> selectHZOffer(List<String> idenNos, String province) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" select ")
                .append(" concat(avy.salary_month, pio.iden_no) id, ")
                .append(" pio.id bizKey, pio.iden_no idenNo, pio.person_name personName, ")
                .append(" smed.s1 realSalary, sbpbk.new_sala_card_no salaCardNo, pet.sala_card_bank salaCardBank ")
                .append(" from ")
                .append(TableName.SALARY_BANK_PAY_BACK).append(" sbpbk ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" on ").append(" sbpbk.person_id = pio.id and pio.is_deleted = 0 ")
                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 and pet.is_stop_salary = 0 ")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" avy ")
                .append(" on ").append(" avy.agency_id = pet.agency_id and avy.is_deleted = 0 and is_month_end = 0 ")
                .append(" and avy.is_confirm_submit='1' and is_pay_data_created = 1 and is_bank_data_created = 1 ")
                .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append(" smed.person_id = pio.id ")
                .append(" where ").append(" sbpbk.is_update = 1 and sbpbk.is_recreate = 0 and sbpbk.mof_div_code = ? ");
        params.add(province);
        if(!CollectionUtils.isEmpty(idenNos)){
            sb.append(" and sbpbk.iden_no in ( ");
            for(String idenNo : idenNos){
                if(idenNos.indexOf(idenNo) != idenNos.size() - 1){
                    sb.append(" ?, ");
                }else{
                    sb.append(" ?) ");
                }
                params.add(idenNo);
            }
        }
        return super.getjdbcTemplate().query(sb.toString(), params.toArray(), new BeanPropertyRowMapper<>(Offer.class));
    }


    //代办异常卡号上报财政
    @Override
    public List<SalaryBankPayBack> expCardNoSub() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" sbpb.id, sbpb.iden_no, sbpb.old_sala_card_no, sbpb.new_sala_card_no, ")
                .append(" sbpb.bank_back_code, sbpb.bank_back_remark, sbpb.operate_code, sbpb.is_update, ")
                .append(" sbpb.verified_in_agency_code, sbpb.verified_code, sbpb.is_recreate, sbpb.is_update_person, sbpb.is_long_use, ")
                .append(" sbpb.is_send,sbpb.person_id,sbpb.agency_id")
                .append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" where ").append("bank_back_code='F' ")
                .append("and");
        sql.append(" verified_in_agency_code ='2' and is_update=1 and verified_code=1 ");
        return super.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryBankPayBack.class));

    }
    //待办单位修改卡号
    @Override
    public List<SalaryBankPayBack> Abnormal() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" sbpb.id, sbpb.iden_no, sbpb.old_sala_card_no, sbpb.new_sala_card_no, ")
                .append(" sbpb.bank_back_code, sbpb.bank_back_remark, sbpb.operate_code, sbpb.is_update, ")
                .append(" sbpb.verified_in_agency_code, sbpb.verified_code, sbpb.is_recreate, sbpb.is_update_person, sbpb.is_long_use, ")
                .append(" sbpb.is_send,sbpb.person_id,sbpb.agency_id")
                .append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" sbpb")
                .append(" where ").append("bank_back_code='F' ")
                .append("and");
        sql.append(" is_send = 1 and verified_in_agency_code is null");
        return super.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryBankPayBack.class));

    }

    @Override
    public List<SalaryBankPayBack> selectNotPushed(String province) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" a.id, a.biz_key,iden_no,person_name, old_sala_card_no, new_sala_card_no, ")
                .append(" bank_back_code, bank_back_remark, operate_code, is_update, ")
                .append(" verified_in_agency_code, verified_code, is_recreate, is_update_person, is_long_use, ")
                .append(" is_send,person_id,agency_id,pay_amt,b.agency_code,b.agency_name ")
                .append(" from ").append(TableName.SALARY_BANK_PAY_BACK).append(" a ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" b ")
                .append(" on a.agency_id=b.id ")
                .append(" where ").append("is_push_to_pay=? and bank_back_code='S'")
                .append("and a.mof_div_code = ? and b.is_deleted=0");
      Object[] params = new Object[]{"0",province};
        return super.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryBankPayBack.class),params);
    }

    @Override
    public void updateIsPushed(String province) {

        StringBuilder sb = new StringBuilder();
        sb.append(" update ").append(TableName.SALARY_BANK_PAY_BACK)
                .append(" set ").append(" is_push_to_pay = 1 ")
                .append(" where ").append("mof_div_code=? ");

        super.getjdbcTemplate().update(sb.toString(),province);
    }
}
